// 项目管理接口
import db;
import response;

// 获取项目列表
var getProjects = () => {
    var sql = """
        SELECT p.*, u.nickname as creator_name,
               (SELECT COUNT(*) FROM tasks t WHERE t.project_id = p.id) as task_count,
               (SELECT COUNT(*) FROM project_members pm WHERE pm.project_id = p.id) as member_count
        FROM projects p
        LEFT JOIN users u ON p.created_by = u.id
        ORDER BY p.created_at DESC
    """;
    var projects = db.select(sql);
    return response.json(projects);
};

// 获取项目详情
var getProjectDetail = (id) => {
    if (!id) {
        return response.json({error: "项目ID不能为空"});
    }
    
    // 项目基本信息
    var projectSql = """
        SELECT p.*, u.nickname as creator_name
        FROM projects p
        LEFT JOIN users u ON p.created_by = u.id
        WHERE p.id = #{id}
    """;
    var project = db.selectOne(projectSql, {id: id});
    
    if (!project) {
        return response.json({error: "项目不存在"});
    }
    
    // 项目成员
    var membersSql = """
        SELECT u.id, u.nickname as name, u.avatar, pm.role
        FROM project_members pm
        LEFT JOIN users u ON pm.user_id = u.id
        WHERE pm.project_id = #{id}
    """;
    var members = db.select(membersSql, {id: id});
    
    // 项目任务统计
    var taskStatsSql = """
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '已完成' THEN 1 END) as completed,
            COUNT(CASE WHEN status != '已完成' THEN 1 END) as in_progress
        FROM tasks
        WHERE project_id = #{id}
    """;
    var taskStats = db.selectOne(taskStatsSql, {id: id});
    
    // 工作流状态
    var workflowSql = """
        SELECT * FROM workflow_statuses
        WHERE project_id = #{id}
        ORDER BY order_num
    """;
    var workflows = db.select(workflowSql, {id: id});
    
    // 最新5个任务
    var tasksSql = """
        SELECT t.*, u.nickname as assignee_name
        FROM tasks t
        LEFT JOIN users u ON t.assignee_id = u.id
        WHERE t.project_id = #{id}
        ORDER BY t.created_at DESC
        LIMIT 5
    """;
    var tasks = db.select(tasksSql, {id: id});
    
    return response.json({
        project: project,
        members: members,
        taskStats: taskStats,
        workflows: workflows,
        tasks: tasks
    });
};

// 创建项目
var createProject = (name, description, createdBy) => {
    if (!name || !createdBy) {
        return response.json({error: "项目名称和创建者不能为空"});
    }
    
    // 开启事务
    var tx = db.transaction();
    try {
        // 创建项目
        var projectId = db.insert("""
            INSERT INTO projects (name, description, created_by)
            VALUES (#{name}, #{description}, #{createdBy})
        """, {
            name: name,
            description: description,
            createdBy: createdBy
        });
        
        // 创建默认工作流状态
        var defaultStatuses = [
            {name: "待处理", color: "#FF9500", order: 1},
            {name: "处理中", color: "#007AFF", order: 2},
            {name: "待测试", color: "#AF52DE", order: 3},
            {name: "已完成", color: "#34C759", order: 4}
        ];
        
        for (var status in defaultStatuses) {
            db.insert("""
                INSERT INTO workflow_statuses (project_id, name, color, order_num)
                VALUES (#{projectId}, #{name}, #{color}, #{order})
            """, {
                projectId: projectId,
                name: status.name,
                color: status.color,
                order: status.order
            });
        }
        
        // 将创建者添加为项目成员
        db.insert("""
            INSERT INTO project_members (project_id, user_id, role)
            VALUES (#{projectId}, #{createdBy}, '项目经理')
        """, {
            projectId: projectId,
            createdBy: createdBy
        });
        
        tx.commit();
        return response.json({id: projectId, message: "项目创建成功"});
    } catch (e) {
        tx.rollback();
        return response.json({error: "项目创建失败: " + e.message});
    }
};

// 更新项目
var updateProject = (id, name, description, status) => {
    if (!id || !name) {
        return response.json({error: "项目ID和名称不能为空"});
    }
    
    var result = db.update("""
        UPDATE projects
        SET name = #{name}, description = #{description}, status = #{status}
        WHERE id = #{id}
    """, {
        id: id,
        name: name,
        description: description,
        status: status
    });
    
    if (result > 0) {
        return response.json({message: "项目更新成功"});
    } else {
        return response.json({error: "项目更新失败"});
    }
};

// 删除项目
var deleteProject = (id) => {
    if (!id) {
        return response.json({error: "项目ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM projects WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "项目删除成功"});
    } else {
        return response.json({error: "项目删除失败"});
    }
};

// 项目成员管理接口

// 获取项目成员列表
var getProjectMembers = (projectId) => {
    if (!projectId) {
        return response.json({error: "项目ID不能为空"});
    }
    
    var sql = """
        SELECT u.id, u.username, u.nickname, u.email, u.avatar, pm.role, pm.joined_at
        FROM project_members pm
        LEFT JOIN users u ON pm.user_id = u.id
        WHERE pm.project_id = #{projectId}
        ORDER BY pm.joined_at DESC
    """;
    
    var members = db.select(sql, {projectId: projectId});
    return response.json(members);
};

// 添加项目成员
var addProjectMember = (projectId, userId, role) => {
    if (!projectId || !userId) {
        return response.json({error: "项目ID和用户ID不能为空"});
    }
    
    try {
        var id = db.insert("""
            INSERT INTO project_members (project_id, user_id, role)
            VALUES (#{projectId}, #{userId}, #{role})
        """, {
            projectId: projectId,
            userId: userId,
            role: role || '成员'
        });
        
        return response.json({id: id, message: "成员添加成功"});
    } catch (e) {
        return response.json({error: "成员添加失败: " + e.message});
    }
};

// 更新项目成员角色
var updateProjectMemberRole = (projectId, userId, role) => {
    if (!projectId || !userId || !role) {
        return response.json({error: "项目ID、用户ID和角色不能为空"});
    }
    
    var result = db.update("""
        UPDATE project_members
        SET role = #{role}
        WHERE project_id = #{projectId} AND user_id = #{userId}
    """, {
        projectId: projectId,
        userId: userId,
        role: role
    });
    
    if (result > 0) {
        return response.json({message: "成员角色更新成功"});
    } else {
        return response.json({error: "成员角色更新失败"});
    }
};

// 删除项目成员
var removeProjectMember = (projectId, userId) => {
    if (!projectId || !userId) {
        return response.json({error: "项目ID和用户ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM project_members
        WHERE project_id = #{projectId} AND user_id = #{userId}
    """, {
        projectId: projectId,
        userId: userId
    });
    
    if (result > 0) {
        return response.json({message: "成员删除成功"});
    } else {
        return response.json({error: "成员删除失败"});
    }
};

// 任务管理接口

// 获取任务列表
var getTasks = (projectId, status, assigneeId) => {
    var sql = """
        SELECT t.*, p.name as project_name, u.nickname as assignee_name, c.nickname as creator_name
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id
        LEFT JOIN users u ON t.assignee_id = u.id
        LEFT JOIN users c ON t.created_by = c.id
        WHERE 1=1
        ?{projectId, AND t.project_id = #{projectId}}
        ?{status, AND t.status = #{status}}
        ?{assigneeId, AND t.assignee_id = #{assigneeId}}
        ORDER BY t.created_at DESC
    """;
    
    var tasks = db.select(sql, {
        projectId: projectId,
        status: status,
        assigneeId: assigneeId
    });
    
    return response.json(tasks);
};

// 获取任务详情
var getTaskDetail = (id) => {
    if (!id) {
        return response.json({error: "任务ID不能为空"});
    }
    
    // 任务基本信息
    var taskSql = """
        SELECT t.*, p.name as project_name, u.nickname as assignee_name, c.nickname as creator_name
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id
        LEFT JOIN users u ON t.assignee_id = u.id
        LEFT JOIN users c ON t.created_by = c.id
        WHERE t.id = #{id}
    """;
    var task = db.selectOne(taskSql, {id: id});
    
    if (!task) {
        return response.json({error: "任务不存在"});
    }
    
    // 任务附件
    var attachmentsSql = """
        SELECT * FROM task_attachments
        WHERE task_id = #{id}
        ORDER BY uploaded_at DESC
    """;
    var attachments = db.select(attachmentsSql, {id: id});
    
    // 任务日志
    var logsSql = """
        SELECT tl.*, u.nickname as user_name
        FROM task_logs tl
        LEFT JOIN users u ON tl.user_id = u.id
        WHERE tl.task_id = #{id}
        ORDER BY tl.created_at DESC
    """;
    var logs = db.select(logsSql, {id: id});
    
    return response.json({
        task: task,
        attachments: attachments,
        logs: logs
    });
};

// 创建任务
var createTask = (projectId, title, description, priority, status, assigneeId, createdById) => {
    if (!projectId || !title || !createdById) {
        return response.json({error: "项目ID、任务标题和创建者不能为空"});
    }
    
    var taskId = db.insert("""
        INSERT INTO tasks (project_id, title, description, priority, status, assignee_id, created_by)
        VALUES (#{projectId}, #{title}, #{description}, #{priority}, #{status}, #{assigneeId}, #{createdById})
    """, {
        projectId: projectId,
        title: title,
        description: description,
        priority: priority || "中",
        status: status || "待处理",
        assigneeId: assigneeId,
        createdById: createdById
    });
    
    // 添加任务创建日志
    db.insert("""
        INSERT INTO task_logs (task_id, user_id, action, description)
        VALUES (#{taskId}, #{userId}, '创建任务', '创建了任务: #{title}')
    """, {
        taskId: taskId,
        userId: createdById,
        title: title
    });
    
    return response.json({id: taskId, message: "任务创建成功"});
};

// 更新任务
var updateTask = (id, title, description, priority, status, assigneeId) => {
    if (!id || !title) {
        return response.json({error: "任务ID和标题不能为空"});
    }
    
    var result = db.update("""
        UPDATE tasks
        SET title = #{title}, description = #{description}, priority = #{priority}, 
            status = #{status}, assignee_id = #{assigneeId}
        WHERE id = #{id}
    """, {
        id: id,
        title: title,
        description: description,
        priority: priority,
        status: status,
        assigneeId: assigneeId
    });
    
    if (result > 0) {
        return response.json({message: "任务更新成功"});
    } else {
        return response.json({error: "任务更新失败"});
    }
};

// 删除任务
var deleteTask = (id) => {
    if (!id) {
        return response.json({error: "任务ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM tasks WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "任务删除成功"});
    } else {
        return response.json({error: "任务删除失败"});
    }
};

// 用户管理接口

// 用户注册
var register = (username, password, nickname, email, phone) => {
    if (!username || !password || !nickname) {
        return response.json({error: "用户名、密码和昵称不能为空"});
    }
    
    // 检查用户名是否已存在
    var existingUser = db.selectOne("""
        SELECT id FROM users WHERE username = #{username}
    """, {username: username});
    
    if (existingUser) {
        return response.json({error: "用户名已存在"});
    }
    
    try {
        var userId = db.insert("""
            INSERT INTO users (username, password, nickname, email, phone)
            VALUES (#{username}, #{password}, #{nickname}, #{email}, #{phone})
        """, {
            username: username,
            password: password, // 实际应用中应该加密存储
            nickname: nickname,
            email: email,
            phone: phone
        });
        
        return response.json({id: userId, message: "注册成功"});
    } catch (e) {
        return response.json({error: "注册失败: " + e.message});
    }
};

// 用户登录
var login = (username, password) => {
    if (!username || !password) {
        return response.json({error: "用户名和密码不能为空"});
    }
    
    var user = db.selectOne("""
        SELECT id, username, nickname, email, avatar, phone
        FROM users
        WHERE username = #{username} AND password = #{password}
    """, {
        username: username,
        password: password
    });
    
    if (user) {
        // 生成一个简单的token（实际应用中应该使用JWT等更安全的方式）
        var token = "token_" + user.id + "_" + Date.now();
        return response.json({user: user, token: token, message: "登录成功"});
    } else {
        return response.json({error: "用户名或密码错误"});
    }
};

// 获取用户信息
var getUserInfo = (id) => {
    if (!id) {
        return response.json({error: "用户ID不能为空"});
    }
    
    var user = db.selectOne("""
        SELECT id, username, nickname, email, avatar, phone, created_at
        FROM users
        WHERE id = #{id}
    """, {id: id});
    
    if (user) {
        return response.json(user);
    } else {
        return response.json({error: "用户不存在"});
    }
};

// 更新用户信息
var updateUserInfo = (id, nickname, email, phone) => {
    if (!id) {
        return response.json({error: "用户ID不能为空"});
    }
    
    var result = db.update("""
        UPDATE users
        SET nickname = #{nickname}, email = #{email}, phone = #{phone}
        WHERE id = #{id}
    """, {
        id: id,
        nickname: nickname,
        email: email,
        phone: phone
    });
    
    if (result > 0) {
        return response.json({message: "用户信息更新成功"});
    } else {
        return response.json({error: "用户信息更新失败"});
    }
};

// 获取所有用户列表
var getUsers = () => {
    var users = db.select("""
        SELECT id, username, nickname, email, avatar, phone, created_at
        FROM users
        ORDER BY created_at DESC
    """);
    
    return response.json(users);
};

// 工作流管理接口

// 获取工作流状态列表
var getWorkflowStatuses = (projectId) => {
    if (!projectId) {
        return response.json({error: "项目ID不能为空"});
    }
    
    var sql = """
        SELECT * FROM workflow_statuses
        WHERE project_id = #{projectId}
        ORDER BY order_num
    """;
    
    var statuses = db.select(sql, {projectId: projectId});
    return response.json(statuses);
};

// 添加工作流状态
var addWorkflowStatus = (projectId, name, color, order) => {
    if (!projectId || !name) {
        return response.json({error: "项目ID和状态名称不能为空"});
    }
    
    try {
        var id = db.insert("""
            INSERT INTO workflow_statuses (project_id, name, color, order_num)
            VALUES (#{projectId}, #{name}, #{color}, #{order})
        """, {
            projectId: projectId,
            name: name,
            color: color || '#007AFF',
            order: order || 0
        });
        
        return response.json({id: id, message: "状态添加成功"});
    } catch (e) {
        return response.json({error: "状态添加失败: " + e.message});
    }
};

// 更新工作流状态
var updateWorkflowStatus = (id, name, color, order) => {
    if (!id || !name) {
        return response.json({error: "状态ID和名称不能为空"});
    }
    
    var result = db.update("""
        UPDATE workflow_statuses
        SET name = #{name}, color = #{color}, order_num = #{order}
        WHERE id = #{id}
    """, {
        id: id,
        name: name,
        color: color,
        order: order
    });
    
    if (result > 0) {
        return response.json({message: "状态更新成功"});
    } else {
        return response.json({error: "状态更新失败"});
    }
};

// 删除工作流状态
var deleteWorkflowStatus = (id) => {
    if (!id) {
        return response.json({error: "状态ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM workflow_statuses WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "状态删除成功"});
    } else {
        return response.json({error: "状态删除失败"});
    }
};

// 团队管理接口

// 获取团队列表
var getTeams = () => {
    var sql = """
        SELECT t.*, u.nickname as creator_name,
               (SELECT COUNT(*) FROM team_members tm WHERE tm.team_id = t.id) as member_count
        FROM teams t
        LEFT JOIN users u ON t.created_by = u.id
        ORDER BY t.created_at DESC
    """;
    var teams = db.select(sql);
    return response.json(teams);
};

// 获取团队详情
var getTeamDetail = (id) => {
    if (!id) {
        return response.json({error: "团队ID不能为空"});
    }
    
    // 团队基本信息
    var teamSql = """
        SELECT t.*, u.nickname as creator_name
        FROM teams t
        LEFT JOIN users u ON t.created_by = u.id
        WHERE t.id = #{id}
    """;
    var team = db.selectOne(teamSql, {id: id});
    
    if (!team) {
        return response.json({error: "团队不存在"});
    }
    
    // 团队成员
    var membersSql = """
        SELECT u.id, u.username, u.nickname, u.email, u.avatar, tm.role, tm.joined_at
        FROM team_members tm
        LEFT JOIN users u ON tm.user_id = u.id
        WHERE tm.team_id = #{id}
        ORDER BY tm.joined_at DESC
    """;
    var members = db.select(membersSql, {id: id});
    
    return response.json({
        team: team,
        members: members
    });
};

// 创建团队
var createTeam = (name, description, createdById) => {
    if (!name || !createdById) {
        return response.json({error: "团队名称和创建者不能为空"});
    }
    
    try {
        var teamId = db.insert("""
            INSERT INTO teams (name, description, created_by)
            VALUES (#{name}, #{description}, #{createdById})
        """, {
            name: name,
            description: description,
            createdById: createdById
        });
        
        // 将创建者添加为团队成员
        db.insert("""
            INSERT INTO team_members (team_id, user_id, role)
            VALUES (#{teamId}, #{createdById}, '团队负责人')
        """, {
            teamId: teamId,
            createdById: createdById
        });
        
        return response.json({id: teamId, message: "团队创建成功"});
    } catch (e) {
        return response.json({error: "团队创建失败: " + e.message});
    }
};

// 更新团队
var updateTeam = (id, name, description) => {
    if (!id || !name) {
        return response.json({error: "团队ID和名称不能为空"});
    }
    
    var result = db.update("""
        UPDATE teams
        SET name = #{name}, description = #{description}
        WHERE id = #{id}
    """, {
        id: id,
        name: name,
        description: description
    });
    
    if (result > 0) {
        return response.json({message: "团队更新成功"});
    } else {
        return response.json({error: "团队更新失败"});
    }
};

// 删除团队
var deleteTeam = (id) => {
    if (!id) {
        return response.json({error: "团队ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM teams WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "团队删除成功"});
    } else {
        return response.json({error: "团队删除失败"});
    }
};

// 团队成员管理接口

// 获取团队成员列表
var getTeamMembers = (teamId) => {
    if (!teamId) {
        return response.json({error: "团队ID不能为空"});
    }
    
    var sql = """
        SELECT u.id, u.username, u.nickname, u.email, u.avatar, tm.role, tm.joined_at
        FROM team_members tm
        LEFT JOIN users u ON tm.user_id = u.id
        WHERE tm.team_id = #{teamId}
        ORDER BY tm.joined_at DESC
    """;
    
    var members = db.select(sql, {teamId: teamId});
    return response.json(members);
};

// 添加团队成员
var addTeamMember = (teamId, userId, role) => {
    if (!teamId || !userId) {
        return response.json({error: "团队ID和用户ID不能为空"});
    }
    
    try {
        var id = db.insert("""
            INSERT INTO team_members (team_id, user_id, role)
            VALUES (#{teamId}, #{userId}, #{role})
        """, {
            teamId: teamId,
            userId: userId,
            role: role || '成员'
        });
        
        return response.json({id: id, message: "成员添加成功"});
    } catch (e) {
        return response.json({error: "成员添加失败: " + e.message});
    }
};

// 更新团队成员角色
var updateTeamMemberRole = (teamId, userId, role) => {
    if (!teamId || !userId || !role) {
        return response.json({error: "团队ID、用户ID和角色不能为空"});
    }
    
    var result = db.update("""
        UPDATE team_members
        SET role = #{role}
        WHERE team_id = #{teamId} AND user_id = #{userId}
    """, {
        teamId: teamId,
        userId: userId,
        role: role
    });
    
    if (result > 0) {
        return response.json({message: "成员角色更新成功"});
    } else {
        return response.json({error: "成员角色更新失败"});
    }
};

// 删除团队成员
var removeTeamMember = (teamId, userId) => {
    if (!teamId || !userId) {
        return response.json({error: "团队ID和用户ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM team_members
        WHERE team_id = #{teamId} AND user_id = #{userId}
    """, {
        teamId: teamId,
        userId: userId
    });
    
    if (result > 0) {
        return response.json({message: "成员删除成功"});
    } else {
        return response.json({error: "成员删除失败"});
    }
};

// 统计接口

// 获取统计概览
var getStatistics = () => {
    // 项目统计
    var projectStats = db.selectOne("""
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '进行中' THEN 1 END) as in_progress,
            COUNT(CASE WHEN status = '已归档' THEN 1 END) as archived
        FROM projects
    """);
    
    // 任务统计
    var taskStats = db.selectOne("""
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '已完成' THEN 1 END) as completed,
            COUNT(CASE WHEN status != '已完成' THEN 1 END) as in_progress
        FROM tasks
    """);
    
    // 用户统计
    var userStats = db.selectOne("""
        SELECT COUNT(*) as total FROM users
    """);
    
    return response.json({
        projects: projectStats,
        tasks: taskStats,
        users: userStats
    });
};

// 获取项目进度统计
var getProjectProgress = () => {
    var sql = """
        SELECT 
            p.name,
            COUNT(t.id) as total_tasks,
            COUNT(CASE WHEN t.status = '已完成' THEN 1 END) as completed_tasks,
            CASE 
                WHEN COUNT(t.id) = 0 THEN 0
                ELSE ROUND(COUNT(CASE WHEN t.status = '已完成' THEN 1 END) * 100.0 / COUNT(t.id), 2)
            END as progress
        FROM projects p
        LEFT JOIN tasks t ON p.id = t.project_id
        GROUP BY p.id, p.name
        ORDER BY progress DESC
    """;
    
    var stats = db.select(sql);
    return response.json(stats);
};